﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting
Imports Microsoft.Reporting.WebForms

Partial Class Reportes_RDLCAPCruce
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
        End If


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
        'generarGrafica()
    End Sub
    Sub cargardatos()
        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            Using da As New SqlDataAdapter

                Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruceCAP", cnn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@Nombre1", If(ddlCampo1.SelectedValue = "0", DBNull.Value, ddlCampo1.SelectedItem.Text))
                    cmd.Parameters.AddWithValue("@Nombre2", If(ddlCampo2.SelectedValue = "0", DBNull.Value, ddlCampo2.SelectedItem.Text))
                    cmd.Parameters.AddWithValue("@Nombre3", If(ddlCampo3.SelectedValue = "0", DBNull.Value, ddlCampo3.SelectedItem.Text))
                    cmd.Parameters.AddWithValue("@Nombre4", If(ddlCampo4.SelectedValue = "0", DBNull.Value, ddlCampo4.SelectedItem.Text))
                    cmd.Parameters.AddWithValue("@campo1", If(ddlCampo1.SelectedValue = "0", DBNull.Value, ddlCampo1.SelectedValue))
                    cmd.Parameters.AddWithValue("@campo2", If(ddlCampo2.SelectedValue = "0", DBNull.Value, ddlCampo2.SelectedValue))
                    cmd.Parameters.AddWithValue("@campo3", If(ddlCampo3.SelectedValue = "0", DBNull.Value, ddlCampo3.SelectedValue))
                    cmd.Parameters.AddWithValue("@campo4", If(ddlCampo4.SelectedValue = "0", DBNull.Value, ddlCampo4.SelectedValue))
                    cmd.Parameters.AddWithValue("@filtro1", ddlfiltro1.SelectedValue)
                    cmd.Parameters.AddWithValue("@filtro2", ddlfiltro2.SelectedValue)
                    cmd.Parameters.AddWithValue("@filtro3", ddlfiltro3.SelectedValue)
                    cmd.Parameters.AddWithValue("@filtro4", ddlfiltro4.SelectedValue)

                    da.SelectCommand = cmd

                    Using thisDTS As New DataSet
                        da.Fill(thisDTS)
                        Dim datasourse As New ReportDataSource("dtsHogares", thisDTS.Tables(0))
                        ReportViewer1.LocalReport.DataSources.Clear()
                        ReportViewer1.LocalReport.DataSources.Add(datasourse)

                    End Using
                End Using
            End Using
        End Using

        'Formatear columnas de datos
        'Dim columnas As Integer = grdResultado.AutoGenerateColumns
        'Dim columnas As Integer = 6
        'grdResultado.Columns(columnas - 1).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 2).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 3).ItemStyle.HorizontalAlign = HorizontalAlign.Right
        'grdResultado.Columns(columnas - 4).ItemStyle.HorizontalAlign = HorizontalAlign.Right




    End Sub
    Protected Sub btnExpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles btnExpt.Click

    End Sub


    Sub llenarCombos()
        Dim u As New Web_Usuario
        Using h As New Helper
            u = h.GetUsuario(Context.User.Identity.Name)
        End Using
        'Using bd As New BdHogaresDataContext
        '    Dim a = From i In bd.Web_Proyectos
        '            Where (i.web_Operadore.idOperador = u.IdOperador) Or IsNothing(u.IdOperador)
        '            Select i.idProyecto, i.CodigoProyecto
        '    ddlProyectos.DataSource = a
        '    ddlProyectos.DataValueField = "CodigoProyecto"
        '    ddlProyectos.DataTextField = "CodigoProyecto"
        '    ddlProyectos.DataBind()
        '    ddlProyectos.Items.Insert(0, New ListItem("Todos", "0"))

        'End Using
        'Using bd As New bdReportesDataContext

        '    ddlMunicipio.DataSource = bd.usp_MunicipiosEstrategia(Context.User.Identity.Name)
        '    ddlMunicipio.DataValueField = "codMunicipio"
        '    ddlMunicipio.DataTextField = "Municipio"
        '    ddlMunicipio.DataBind()
        '    ddlMunicipio.Items.Insert(0, New ListItem("Todos", "0"))
        'End Using


        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()
            LlenarItems(ddlCampo1, "RPTCAPAgrupado", cnn)
            LlenarItems(ddlCampo2, "RPTCAPAgrupado", cnn)
            LlenarItems(ddlCampo3, "RPTCAPAgrupado", cnn)
            LlenarItems(ddlCampo4, "RPTCAPAgrupado", cnn)
            ddlCampo1.Items.Insert(0, New ListItem("Sin variable", "0"))
            ddlCampo2.Items.Insert(0, New ListItem("Sin variable", "0"))
            ddlCampo3.Items.Insert(0, New ListItem("Sin variable", "0"))
            ddlCampo4.Items.Insert(0, New ListItem("Sin variable", "0"))
            cnn.Close()
        End Using
    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub

    Protected Sub ddlCampo1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo1.SelectedIndexChanged
        Cargarfiltros(ddlCampo1, ddlfiltro1)
    End Sub

    Sub Cargarfiltros(ByRef campo As DropDownList, ByRef filtro As DropDownList)
        If campo.SelectedValue <> "0" Then

            Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
                cnn.Open()

                Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruceCAPFiltro", cnn)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@campo1", campo.SelectedValue)
                    filtro.DataSource = cmd.ExecuteReader
                    filtro.DataTextField = "detalle"
                    filtro.DataValueField = "valor"
                    filtro.DataBind()
                    filtro.Items.Insert(0, New ListItem("Todos", "Todos"))
                End Using

                cnn.Close()

            End Using
        Else
            filtro.Items.Clear()
        End If

    End Sub

    Protected Sub ddlCampo2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo2.SelectedIndexChanged
        Cargarfiltros(ddlCampo2, ddlfiltro2)
    End Sub

    Protected Sub ddlCampo3_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo3.SelectedIndexChanged
        Cargarfiltros(ddlCampo3, ddlfiltro3)
    End Sub

    Protected Sub ddlCampo4_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCampo4.SelectedIndexChanged
        Cargarfiltros(ddlCampo4, ddlfiltro4)
    End Sub
End Class
